Excel Solutions for Accountants: Book 3 by Duncan Williamson

Author:Duncan Williamson
Language: eng
Format: epub
Publisher: Bookboon.com Ltd.
Published: 2015-07-02T00:00:00+00:00

5.1.1 Income Statement Forecasts

In this example we are given some basic inputs for an organisation, see Figure 4. You are shown the completed basic Income Statement Schedule too and you should aim to prepare that statement.

Figure 4 Basic Income Statement Forecast

This is a forecast: not a budget, which will come later.

Phase One

For phase one of this forecast, you are told the maximum annual demand as well as the annual capacity level: cells A4:B5 and you must program the Revenue row in the income statement forecast to deal with the difference between them. Your formula here must be able to cope with changes to the demand and capacity as management makes changes or suggests changes to these figures.

You can also see that there is some building work to be done, which is a capital expenditure item. However, that building needs to be depreciated over the life of the project or department or asset: 10 years. We have programmed the straight line depreciation calculations based on a scrap value of £4,000 receivable at the end of the project.

That is the end of Phase One and your forecast should look like the one in Figure 4, above. All of this is in the forecast.xlsx file, income (1) tab.

Phase Two

For phase two there are additional items to take into account: having considered your Phase One forecasts, the directors have asked you to take the following into account.

There will be additional building work worth £5,000 in Year 3 with a scrap value of £1,000 and further building work worth £7,500 with a scrap value of £2,000 in year 7

Partly as a result of the building enhancements, the directors believe that the annual capacity will grow at the following rates in the following years shown in Table 1 below: Year

Growth (%)

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.